﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using CommonLibrary.Data.Model;

namespace CommonLibrary.Data.Sqlite
{
    public class SqlitePublicDAL
    {
        #region GetConnectionString

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <returns></returns>
        public static DataTable GetConnectionTable(string in_FullName)
        {
            if (File.Exists(in_FullName) == false)
            {
                throw new Exception(in_FullName + "不存在！");
            }

            DataSet ds = new DataSet();
            ds.ReadXml(in_FullName);
            if (ds.Tables[0].ClIsNullOrEmpty())
            {
                throw new Exception(in_FullName + "文件为空！");
            }

            return ds.Tables[0];
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static string GetConnectionStringFull(string in_FullName, int index)
        {
            DataTable dt = GetConnectionTable(in_FullName);
            if (index >= dt.Rows.Count)
            {
                throw new Exception(index.ToString() + "大于下限值！");
            }
            DataRow row = dt.Rows[index];

            return "Data Source=" + Convert.ToString(row["server"]) + ";User ID=" + Convert.ToString(row["uid"]) + ";Password=" + Convert.ToString(row["pwd"]).ClDecrypt3DES() + ";";
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <returns></returns>
        public static string GetConnectionStringFull(string in_FullName)
        {
            return GetConnectionStringFull(in_FullName, 0);
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <param name="in_Description"></param>
        /// <returns></returns>
        public static string GetConnectionStringFull(string in_FullName, string in_Description)
        {
            DataTable dt = GetConnectionTable(in_FullName);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (Convert.ToString(dt.Rows[i]["description"]).ClToUpperTrim() == in_Description.ClToUpperTrim())
                {
                    return GetConnectionStringFull(in_FullName, i);
                }
            }

            throw new Exception(in_Description + "配置不存在！");
        }

        /// <summary>
        /// 获取连接文件
        /// </summary>
        /// <param name="in_File"></param>
        /// <returns></returns>
        public static string GetConnectionFile(string in_File)
        {
            string str = @"D:\INI\" + in_File;
            if (File.Exists(str))
            {
                return str;
            }

            str = FileClass.GetStartupPath() + in_File;
            if (File.Exists(str))
            {
                return str;
            }

            throw new Exception(in_File + "不存在！");
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_File"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static string GetConnectionString(string in_File, int index)
        {
            return GetConnectionStringFull(GetConnectionFile(in_File), index);
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_File"></param>
        /// <returns></returns>
        public static string GetConnectionString(string in_File)
        {
            return GetConnectionStringFull(GetConnectionFile(in_File));
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_File"></param>
        /// <param name="in_Description"></param>
        /// <returns></returns>
        public static string GetConnectionString(string in_File, string in_Description)
        {
            return GetConnectionStringFull(GetConnectionFile(in_File), in_Description);
        }

        /// <summary>
        /// 获取连接字符串中的描述
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static string GetConnectionDescription(string in_ConnectionString)
        {
            string[] arr = in_ConnectionString.ClSplitString(";");
            return arr[0];
        }

        /// <summary>
        /// 获取连接字符串中的服务器
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static string GetConnectionServer(string in_ConnectionString)
        {
            string[] arr = in_ConnectionString.ClSplitString(";");
            return arr[1];
        }


        /// <summary>
        /// 生成连接文件
        /// </summary>
        public static void ShowConnectionFileDemo()
        {
            #region MyRegion

            string str = @"<NewDataSet>
  <Table>
    <description>MJY</description>
    <server>dev</server>    
    <uid>mjy</uid>
    <pwd>使用3DES加密WPT1ZPJaw98=</pwd>    
  </Table>
  <Table>
    <description>MSP</description>
    <server>dev</server>    
    <uid>msp</uid>
    <pwd>使用3DES加密EkAKWcD0mio=</pwd>    
  </Table>
</NewDataSet>";


            #endregion

            string txfile = FileClass.GetTempFile(".xml");
            using (StreamWriter sw = new StreamWriter(txfile, true, Encoding.UTF8))
            {
                sw.Write(str);
            }
            ("notepad.exe " + txfile).ClRunAsDos();
        }

        #endregion


        /// <summary>
        /// CopyListSQLiteParameter，带参数listParameter Execute一次后，再Execute会出错“另一个 SQLiteParameterCollection 中已包含 SQLiteParameter”，
        /// 因为SQLiteParameter必须要new一个新的
        /// </summary>
        /// <param name="listParameter"></param>
        /// <returns></returns>
        public static List<SQLiteParameter> CopyListSQLiteParameter(List<SQLiteParameter> listParameter)
        {
            List<SQLiteParameter> listParameterNew = new List<SQLiteParameter>();
            foreach (var p in listParameter.ToArray())
            {
                listParameterNew.Add((SQLiteParameter)((ICloneable)p).Clone());
            }
            return listParameterNew;
        }

        /// <summary>
        /// 字典转为参数
        /// </summary>
        /// <param name="in_action"></param>
        /// <param name="in_dic"></param>
        /// <param name="out_paras"></param>
        /// <param name="out_sql"></param>
        /// <returns></returns>
        public static bool DictionaryToParameters(string in_action, Dictionary<string, object> in_dic, out SQLiteParameter[] out_paras, out string out_sql)
        {
            string str = "";
            List<SQLiteParameter> paras = new List<SQLiteParameter>();
            foreach (KeyValuePair<string, object> keyValuePair in in_dic)
            {
                if (keyValuePair.Key == "RecordStatus")
                {
                    continue;
                }
                if (keyValuePair.Key == "RecordFlag")
                {
                    continue;
                }
                if (keyValuePair.Key == "RecordSignId")
                {
                    continue;
                }
                if (keyValuePair.Key == "RecordSignMsg")
                {
                    continue;
                }
                if (in_action == "select")
                {
                    str += "  and " + keyValuePair.Key + " = :" + keyValuePair.Key;
                    paras.Add(new SQLiteParameter(":" + keyValuePair.Key, keyValuePair.Value));
                }
                if (in_action == "delete")
                {
                    str += "  and " + keyValuePair.Key + " = :" + keyValuePair.Key;
                    paras.Add(new SQLiteParameter(":" + keyValuePair.Key, keyValuePair.Value));
                }
                if (in_action == "update")
                {
                    paras.Add(new SQLiteParameter(":" + keyValuePair.Key, keyValuePair.Value));
                }
                if (in_action == "insert")
                {
                    str += ", " + keyValuePair.Key + "";
                    paras.Add(new SQLiteParameter(":" + keyValuePair.Key, keyValuePair.Value));
                }
            }
            out_sql = str.Substring(2);
            out_paras = paras.ToArray();
            return true;
        }

        /// <summary>
        /// 写数据库日志
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <param name="in_batchNo"></param>
        /// <param name="in_Project"></param>
        /// <param name="in_Type"></param>
        /// <param name="in_Event"></param>
        /// <param name="in_Source"></param>
        /// <param name="isError"></param>
        /// <param name="in_msg"></param>
        /// <returns></returns>
        public static bool Log(string in_ConnectionString, string in_batchNo, string in_Project, string in_Type, string in_Event, string in_Source, bool isError, string in_msg)
        {
            try
            {
                #region 必须建有这个表

                //  CREATE TABLE LOGMESSAGE (
                //  TXID VARCHAR2(100 BYTE) DEFAULT sys_guid(),
                //  TXMATCHID VARCHAR2(100 BYTE) DEFAULT sys_guid(),
                //  TXPROJECTTYPE VARCHAR2(100 BYTE) DEFAULT '',
                //  TXTYPE VARCHAR2(100 BYTE) DEFAULT '',
                //  TXEVENT VARCHAR2(100 BYTE) DEFAULT '',
                //  TXSOURCE VARCHAR2(300 BYTE) DEFAULT '',
                //  TXTIME TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
                //  TXUSER VARCHAR2(30 BYTE) DEFAULT USER,
                //  TXISERROR NUMBER DEFAULT 1,
                //  TXMSG CLOB DEFAULT '',
                //  CONSTRAINT PK_LOGMESSAGE PRIMARY KEY (TXID));

                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXEVENT IS '事件类型(访问ShopCart接口，返回ShopCart结果)';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXID IS 'ID';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXISERROR IS '是否错误';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXMATCHID IS '匹配TXID';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXMSG IS '内容';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXPROJECTTYPE IS '项目类型(DataInterface)';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXSOURCE IS '来源(http://demodatainterfacemall.miugo.net/handler/DataInterface.ashx?action=createpayment&t=1366187470917)';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXTIME IS '时间';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXTYPE IS '日志类型(ShopCart)';
                //COMMENT ON COLUMN MPUB.LOGMESSAGE.TXUSER IS '用户';

                #endregion

                #region 写到数据库

                string TXID = Guid.NewGuid().ToString().ToUpper();

                string TXMATCHID = in_batchNo;
                if (TXMATCHID.ClIsNullOrWhiteSpace())
                {
                    TXMATCHID = TXMATCHID.ClMD5String();
                }

                string RsString = "select count(*) from logmessage where txid = :txid";
                if (ClSqlite.CheckIsEmpty(in_ConnectionString, RsString, new SQLiteParameter[] { new SQLiteParameter(":txid", TXMATCHID) }))
                {
                    TXID = TXMATCHID;
                }



                string TXEVENT = in_Event;
                if (TXEVENT.ClIsNullOrWhiteSpace())
                {
                    TXEVENT = TXEVENT.ClMD5String();
                }
                string TXSOURCE = in_Source;
                try
                {
                    if (TXSOURCE.ClIsNullOrWhiteSpace())
                    {
                        TXSOURCE = Convert.ToString(System.Web.HttpContext.Current.Request.Url.ToString());
                    }
                }
                catch { }
                string TXISERROR = "0";
                if (isError)
                {
                    TXISERROR = "1";
                }
                string TXMSG = in_msg;



                using (SQLiteConnection cn = new SQLiteConnection(in_ConnectionString))
                {
                    cn.Open();
                    SQLiteTransaction tran = cn.BeginTransaction();

                    RsString = @"insert into logmessage(TXID, TXMATCHID, TXPROJECTTYPE, TXTYPE, TXEVENT, TXSOURCE, TXISERROR) values(:TXID, :TXMATCHID, :TXPROJECTTYPE, :TXTYPE, :TXEVENT, :TXSOURCE, :TXISERROR)";

                    tran.ClExecuteNonQuery(RsString, new SQLiteParameter[]
                                                         {
                                                             new SQLiteParameter(":TXID", TXID),
                                                             new SQLiteParameter(":TXMATCHID", TXMATCHID),
                                                             new SQLiteParameter(":TXPROJECTTYPE", in_Project),
                                                             new SQLiteParameter(":TXTYPE", in_Type),
                                                             new SQLiteParameter(":TXEVENT", TXEVENT),
                                                             new SQLiteParameter(":TXSOURCE", TXSOURCE),
                                                             new SQLiteParameter(":TXISERROR", TXISERROR)
                                                         });

                    RsString = @"update logmessage set TXMSG = :TXMSG where TXID = :TXID";
                    tran.ClExecuteNonQuery(RsString, new SQLiteParameter[] { new SQLiteParameter(":TXMSG", TXMSG), new SQLiteParameter(":TXID", TXID) });

                    tran.Commit();
                }

                #endregion

                return true;
            }
            catch (Exception err)
            {
                FileClass.Log(err.Message, err.StackTrace);
                return false;
            }
        }

        ///// <summary>
        ///// 获取分页数据
        ///// </summary>
        ///// <param name="in_ConnectionString"></param>
        ///// <param name="in_Sql"></param>
        ///// <param name="in_PageSize"></param>
        ///// <param name="in_PageNum"></param>
        ///// <param name="out_Rows"></param>
        ///// <param name="out_Pages"></param>
        ///// <returns></returns>
        //public static DataTable GetPageData(string in_ConnectionString, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        //{
        //    //必须建有P_GETPAGEDATA存储过程

        //    out_Rows = -1;
        //    out_Pages = -1;

        //    SQLiteParameter para_out_Rows = new SQLiteParameter("out_Rows", OracleType.Int32) { Direction = ParameterDirection.Output };
        //    SQLiteParameter para_out_Pages = new SQLiteParameter("out_Pages", OracleType.Int32) { Direction = ParameterDirection.Output };

        //    SQLiteParameter[] paras = new SQLiteParameter[]
        //    {
        //        new SQLiteParameter("in_Sql", in_Sql),
        //        new SQLiteParameter("in_PageSize", in_PageSize),
        //        new SQLiteParameter("in_PageNum", in_PageNum),
        //        para_out_Rows,
        //        para_out_Pages,
        //        new SQLiteParameter("out_table", OracleType.Cursor){Direction = ParameterDirection.Output}
        //    };

        //    DataTable dt = ClSqlite.ExecuteDataTable(in_ConnectionString, CommandType.StoredProcedure, "P_GETPAGEDATA", paras);
        //    out_Rows = Convert.ToInt32(para_out_Rows.Value.ToString());
        //    out_Pages = Convert.ToInt32(para_out_Pages.Value.ToString());
        //    return dt;
        //}


        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static List<ModelOracleTable> GetTablesInfo(string in_ConnectionString)
        {
            string RsString = @"select OWNER, OBJECT_NAME TABLE_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME from all_objects  where object_type='TABLE' and owner = USER ORDER BY object_name";
            DataTable dtSourceTables = ClSqlite.ExecuteDataTable(in_ConnectionString, RsString);
            return DataExtensions.DataTableToList<ModelOracleTable>(dtSourceTables);
        }

        /// <summary>
        /// 获取列信息
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static List<ModelOracleColumn> GetColumnsInfo(string in_ConnectionString, string in_table)
        {
            string RsString = @"select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH, GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED, V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN, SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME from user_tab_cols where table_name = :table_name order by COLUMN_ID";
            DataTable dtSourceCol = ClSqlite.ExecuteDataTable(in_ConnectionString, RsString, new SQLiteParameter[] { new SQLiteParameter(":table_name", in_table) });
            return DataExtensions.DataTableToList<ModelOracleColumn>(dtSourceCol);
        }

    }
}
